#!/usr/bin/perl

use Encode;
use FileHandle;
use IPC::Open2;
use Data::Dumper;
use HTTP::Async;
use HTTP::Request; 
use Date::Parse;
use Digest::SHA qw(sha256_hex);
use DBI;
use String::Escape qw( printable unprintable );
use Getopt::Std;

use strict;

my $cert_dir="/tmp/certs";
my $crl_dir="/tmp/crls";
my %crl;

my %db_id;
my $dbh;
my %options=();

sub insert_cert_into_db{
   my ($cert_sha256_fp, $issuer, $valid_from, $valid_to, $pk_size, $public_key_alg,$self_signed)=@_;
   my $public_key_alg_db_id; 

   #we might have a shortcut
   if (defined $db_id{'cert_id'}{$cert_sha256_fp}) {
     return $db_id{'cert_id'}{$cert_sha256_fp};
   }
   if (defined  $db_id{'public_key_alg_db_id'}{$public_key_alg}){
     $public_key_alg_db_id=$db_id{'public_key_alg_db_id'}{$public_key_alg};
   }
   else {
     my $insert_key_alg_query_text="INSERT IGNORE into public_key_alg (name) VALUES (?)";
     my $insert_key_alg_query_sth=$dbh->prepare( $insert_key_alg_query_text) or die;
     $insert_key_alg_query_sth->execute($public_key_alg) or die;
   
     my $select_key_alg_id_text="SELECT public_key_alg_id from  public_key_alg where name=?";
     my $select_key_alg_id_sth=$dbh->prepare( $select_key_alg_id_text) or die;
     $select_key_alg_id_sth->execute($public_key_alg) or die;    
     if(my @result=$select_key_alg_id_sth->fetchrow_array()){
       $public_key_alg_db_id=$result[0];
       $db_id{'public_key_alg_db_id'}{$public_key_alg}=$public_key_alg_db_id;
     }
     else{
       die "did not got back inserted val public_key_alg=$public_key_alg\n";
     }     
   }

   ##now the cert
   my $insert_cert_query_text="INSERT IGNORE into cert ( pem_sha256_hex,issuer_org,valid_from,valid_to,public_key_size_bits,public_key_alg_id, self_signed) VALUES (?,?,?,?,?,?,?)";
   my $insert_cert_query_sth=$dbh->prepare( $insert_cert_query_text) or die;
   $insert_cert_query_sth->execute($cert_sha256_fp, $issuer, $valid_from, $valid_to, $pk_size, $public_key_alg_db_id, $self_signed) or die;
   
   my $select_cert_id_text="SELECT cert_id from  cert where pem_sha256_hex=?";
   my $select_cert_id_sth=$dbh->prepare( $select_cert_id_text) or die;
   $select_cert_id_sth->execute($cert_sha256_fp) or die;
   if(my @result=$select_cert_id_sth->fetchrow_array()){
     #$public_key_alg_db_id=$result[0];
     my $cert_db_id=$result[0];
     $db_id{'cert_id'}{$cert_sha256_fp}=$cert_db_id;
     return $cert_db_id;
   }
   else{
     die "did not got back inserted val for cert cert_fp=$cert_sha256_fp\n";
   }  
}

sub insert_connection_info{
   my ($cert_db_id,$hostname,$epoch,$ssl_cipher,$ssl_protocol,$compression)=@_;
   my $ssl_cipher_db_id;
   my $ssl_protocol_db_id;

   ##get the 
  
   if (defined  $db_id{'ssl_cipher_db_id'}{$ssl_cipher}) {
     $ssl_cipher_db_id=$db_id{'ssl_cipher_db_id'}{$ssl_cipher};
   }
   else{
     my $insert_ssl_cipher_query_text="INSERT IGNORE into SSL_cipher (name) VALUES (?)";
     my $insert_ssl_cipher_sth=$dbh->prepare( $insert_ssl_cipher_query_text) or die;
     $insert_ssl_cipher_sth->execute($ssl_cipher) or die;

     my $select_ssl_cipher_id_text="SELECT SSL_cipher_id from SSL_cipher where name=?";
     my $select_ssl_cipher_id_sth=$dbh->prepare( $select_ssl_cipher_id_text) or die;
     $select_ssl_cipher_id_sth->execute($ssl_cipher) or die;
     if(my @result=$select_ssl_cipher_id_sth->fetchrow_array()){
       $ssl_cipher_db_id=$result[0];
       $db_id{'ssl_cipher_db_id'}{$ssl_cipher}=$ssl_cipher_db_id;
     }
     else{
       die "did not got back inserted val ssl_cipher=$ssl_cipher\n";
     }
   }
  
   if(defined  $db_id{'ssl_protocol_db_id'}{$ssl_protocol}){
       $ssl_protocol_db_id=$db_id{'ssl_protocol_db_id'}{$ssl_protocol};
   }
   else{
     my $insert_ssl_protocol_query_text="INSERT IGNORE into SSL_protocol (name) VALUES (?)";
     my $insert_ssl_protocol_sth=$dbh->prepare( $insert_ssl_protocol_query_text) or die;
     $insert_ssl_protocol_sth->execute($ssl_protocol) or die;

     my $select_ssl_protocol_id_text="SELECT SSL_protocol_id from SSL_protocol where name=?";
     my $select_ssl_protocol_id_sth=$dbh->prepare( $select_ssl_protocol_id_text) or die;
     $select_ssl_protocol_id_sth->execute($ssl_protocol) or die;
     if(my @result=$select_ssl_protocol_id_sth->fetchrow_array()){
       $ssl_protocol_db_id=$result[0];
       $db_id{'ssl_protocol_db_id'}{$ssl_protocol}=$ssl_protocol_db_id;
     }
     else{
       die "did not got back inserted val ssl_protocol=$ssl_protocol\n";
     }
   }

   ## now the connection
   my $connection_db_id;

   my $insert_connection_query_text="INSERT IGNORE into connection (hostname,epoch,SSL_cipher_id,SSL_protocol_id,compression) VALUES (?,?,?,?,?)";
   my $insert_connection_sth=$dbh->prepare( $insert_connection_query_text) or die;
   $insert_connection_sth->execute($hostname,$epoch,$ssl_cipher_db_id,$ssl_protocol_db_id,$compression) or die;


   my $select_connection_id_text="SELECT connection_id from connection where hostname=? and epoch=? ";
   my $select_connection_id_sth=$dbh->prepare( $select_connection_id_text) or die;
   $select_connection_id_sth->execute($hostname,$epoch) or die;
   if(my @result=$select_connection_id_sth->fetchrow_array()){
     $connection_db_id=$result[0];
     #$db_id{'ssl_protocol_db_id'}{$ssl_protocol}=$ssl_protocol_db_id;
   }
   else{
     die "did not got back inserted val connection_host=$hostname, epoch=$epoch\n";
   }

   ##now the cert-con mapping!
   my $insert_cert_connection_map_query_text="INSERT IGNORE into cert_connection_map (cert_id,connection_id) VALUES (?,?)";
   my $insert_cert_connection_map_sth=$dbh->prepare( $insert_cert_connection_map_query_text) or die;
   $insert_cert_connection_map_sth->execute($cert_db_id,$connection_db_id) or die;

}

sub insert_ocsp_url_set{
   my ($cert_db_id,$urlset_href)=@_; 

   my $insert_ocsp_url_text="INSERT ignore into ocsp (url) VALUES (?)";
   my $insert_ocsp_url_sth=$dbh->prepare($insert_ocsp_url_text) or die;   
   
   my $select_url_db_id_text="SELECT ocsp_id from ocsp where url=?";
   my $select_url_db_id_sth=$dbh->prepare($select_url_db_id_text) or die;

   my $insert_cert_ocsp_map_text="INSERT ignore into cert_ocsp_map (cert_id,ocsp_id) VALUES (?,?)";
   my $insert_cert_ocsp_sth=$dbh->prepare($insert_cert_ocsp_map_text) or die;

   foreach my $url (@{$urlset_href}){
     my $ocsp_db_id;
     if(defined $db_id{'ocsp_db_id'}{$url}){
       $ocsp_db_id=$db_id{'ocsp_db_id'}{$url};
     }
     else{
       $insert_ocsp_url_sth->execute($url) or die;
       $select_url_db_id_sth->execute($url) or die;

       if(my @result=$select_url_db_id_sth->fetchrow_array()){
         $ocsp_db_id=$result[0];
         $db_id{'ocsp_db_id'}{$url}=$ocsp_db_id;
       }
       else{
         die "did not got back inserted val ocsp url=$url\n";
       }
     }
     $insert_cert_ocsp_sth->execute($cert_db_id, $ocsp_db_id) or die;
   }
}

sub get_crl_from_file{

  my ($in_filename,$prefix) = @_;
  my $filename=$prefix."/".$in_filename;
  my $cert_pem;
  open (FH, $filename) or die "cannot open $filename";

  my ($dev,$ino,$mode,$nlink,$uid,$gid,$rdev,$size,
$atime,$mtime,$ctime,$blksize,$blocks) = stat FH;

  my $SSL_cipher;
  my $SSL_protocol;
  my $SSL_compression;

  my $cert_done=0;
  while(<FH>){
    my $line=$_;
    if($line =~ /-----BEGIN CERTIFICATE----/){
      $cert_pem.=$line;
      next;
    }

    if($cert_done ==0 && length($cert_pem)>0){
      $cert_pem.=$line;
    }
    if($line =~ /-----END CERTIFICATE----/){
      $cert_done=1;
      next;
    }
    if($line =~ /Expansion:\s+(.*)\s*$/){
      my $compression_text=$1;
      if($compression_text =~ /NONE/ ){
        $SSL_compression=0;
      }
      else{
        $SSL_compression=1;
      }
    }
    if($line =~ / Protocol\s+:\s+(.*)\s*$/){
      $SSL_protocol=$1;
    }
    if($line =~ / Cipher\s+:\s+(.*)\s*$/){
      $SSL_cipher=$1;
    }
  }
  close(FH);
  

  #print $cert_pem;
  if(length($cert_pem)<=0){
     return;
  }

  my $issuer_org;
  my $full_issuer_org;
  my $full_subject_org;
  my $self_signed=0;
  my $public_key_alg_name;
  my $public_key_alg_size;
  my $cert_expiration;
  my $cert_creation;
  my $pem_sha256=sha256_hex($cert_pem);
  my @crl_set;
  my @ocsp_set;
  #now we process the cert via openssl
  my $openssl_pid = open2(*Reader, *Writer, "openssl x509 -text ");
  print Writer "$cert_pem\n";
  close Writer;
  while(<Reader>){
    my $line=$_;
    if($line =~ /Issuer:\s*(.*)$/){
      #$issuer_org=$1;
      $full_issuer_org=$1;
      $issuer_org=$full_issuer_org;
      if( $issuer_org =~ /^DC=([^,]+),\s*DC=([^,]+)/){
        $issuer_org=$2.".".$1;
      }      
      if ($issuer_org =~ /.*O=([^,]+)/){
        my $org=$1;
        my $old_length=length($org);
        if(length($org)>3){
          $org =~ s/\\x([0-9A-Fa-f]+)/chr(hex($1))/ge;
          utf8::decode($org);
=cut
          if(length($org) != $old_length || $in_filename =~ /edu.tw/ ){
            print "org=$org";
            #utf8::encode($org);
            $org=unprintable($full_issuer_org);
            utf8::decode($org);
            die "org_on_die='$org'";
          }
=cut 
         $issuer_org =$org;
        }
      }
      #print "issuer='$issuer_org'\n"
    }
    if($line =~ /Subject:\s*(.*)$/){
      $full_subject_org=$1;
      if($full_subject_org eq $full_issuer_org){
        $self_signed=1;
        $issuer_org=$full_issuer_org;
        #die "self-signed filename=$filename\n";
      }
    }

=cut
    if($line =~ /Issuer:\s*DC=([^,]+),\s*DC=([^,]+)/){
      my $issuer_line=$1;
      $issuer_org=$2.".".$1;
      #print "issuer='$issuer_org'\n";
    }
    if($line =~ /Issuer:.*O=([^,]+),/){
      $issuer_org=$1;
      #print "issuer='$issuer_org'\n";
    }
=cut

    if($line =~ /Not After :(.*)$/){
      my $exp_text=$1;
      $cert_expiration=str2time($exp_text);
      #print "exp: $cert_expiration ($exp_text)\n";
    }
    if($line =~ / Not Before:(.*)$/){
      my $exp_creat=$1;
      $cert_creation=str2time($exp_creat);
      #print "exp: $cert_expiration ($exp_text)\n";
    }

    if($line =~ /Public Key Algorithm\s*:\s*(.*)\s*/){
      $public_key_alg_name=$1;
      print "pk_name: '$public_key_alg_name'\n";
    }
    if($line =~ /Public-Key\s*:[^0123456789]+(\d+)[^0123456789]/){
      $public_key_alg_size=$1;
      print "pk_size: '$public_key_alg_size'\n";
    }

    if ($line =~/^\s+URI:(http.*crl.*)$/){
      my $url=$1;
      push(@{$crl{$url}{'hosts'}},$in_filename);
      $crl{$url}{'issuer'}=$issuer_org;
      push(@{$crl{$url}{'cert_sha256'}},$pem_sha256);
      push(@crl_set,$url);
    }
    if ($line =~/OCSP.*URI:(http.*)$/){
      my $url=$1;
      #push(@{$crl{$url}{'hosts'}},$in_filename);
      #$crl{$url}{'issuer'}=$issuer_org;
      push(@ocsp_set,$url);
    }
    #print $line;
  }
  close Reader;
  #print Dumper(\%crl);

  ##do db stuff
  #insert ignore public_key_alg
  #get public_key_alg_id
  #insert ignore cert
  #get cert id
  #insert ignore SSL_cipher_id
  #insert ignore SSL_protocol_id
  #insert ignore connection
  #get site db_id
  #insert_ignore_crls!
  #insert ignore cert-crlmap
  #insert ignore ocsp
  #get oscp id
  #insert crt-ocsp-map

  ##lets begin
  my $cert_db_id=insert_cert_into_db($pem_sha256, $issuer_org, $cert_creation, $cert_expiration, $public_key_alg_size, $public_key_alg_name,$self_signed);
  
  ##
  insert_connection_info($cert_db_id,$in_filename,$ctime,$SSL_cipher,$SSL_protocol,$SSL_compression);

  insert_ocsp_url_set($cert_db_id,\@ocsp_set);

  #die;
}

sub get_crls{

  opendir(my $dh, $cert_dir) || die;
  while(readdir $dh) {
    my $filename="$cert_dir/$_";
    #print "$cert_dir/$_\n";
    get_crl_from_file($_,$cert_dir);
  }
  closedir $dh;
  #print Dumper(\%crl);
}


sub process_async_responses{
  my ($async)=@_;
  while ( my $response = $async->wait_for_next_response ) {
    my $begin_size=scalar(keys %crl);
    #print STDERR "size=$begin_size\n";

        # Do some processing with $response
    my $url=$response->base;
    my $status=$response->code;
    my $raw_size=length($response->content);
    #print "$status $url $size\n";
    $crl{$url}{'code'}=$status;
    $crl{$url}{'raw_size'}=$raw_size;

    my $new_size=scalar(keys %crl);
    if($new_size<$begin_size){
      die;
    }
  }

}

sub fetch_crls{

  my $async = HTTP::Async->new;
  $async->slots(20);
  #$async->timeout(15);
  #$async->max_request_time(30);
  
  foreach my $crl_url (keys %crl) {
      #if(@{$crl{$crl_url}{'hosts'}}<2){
      #  next;
      #}
      $async->add( HTTP::Request->new( GET => $crl_url) );
  }
  process_async_responses($async);
}


sub insert_crl_into_db{
  my ($crl_url,$status,$size) = @_;
  #my $cert_db_id= $db_id{'cert_id'}{$crl{$crl_url}{'cert_sha256'}};  
  my $crl_db_id;

  if(defined $db_id{'crl_db_id'}{$crl_url}) {
    $crl_db_id=$db_id{'crl_db_id'}{$crl_url}
  }  
  else{
    my $insert_crl_query_text="INSERT ignore into crl (url,http_response, size) VALUES (?,?,?) ";
    my $insert_crl_sth=$dbh->prepare($insert_crl_query_text) or die;
    $insert_crl_sth->execute($crl_url,$status,$size) or die;

    my $select_crl_id_query_text="SELECT crl_id from crl where url=?";
    my $select_crl_id_query_sth=$dbh->prepare($select_crl_id_query_text) or die;
    $select_crl_id_query_sth->execute($crl_url);
    #   $select_ssl_protocol_id_sth->execute($ssl_protocol) or die;
    if(my @result=$select_crl_id_query_sth->fetchrow_array()){
       $crl_db_id=$result[0];
       $db_id{'crl_db_id'}{$crl_url}=$crl_db_id;
    }
     else{
      die "did not got back inserted val crl=$crl_url\n";
    }
  }
  ##now the mapping
  foreach my  $cert_sha256 (@{$crl{$crl_url}{'cert_sha256'}}){
    my $cert_db_id= $db_id{'cert_id'}{$cert_sha256};
    my $insert_cert_crl_map_query_text="INSERT IGNORE into cert_crl_map (cert_id,crl_id) VALUES (?,?)";
    my $insert_cert_crl_map_sth=$dbh->prepare($insert_cert_crl_map_query_text) or die;
    $insert_cert_crl_map_sth->execute($cert_db_id,$crl_db_id) or die;
  }

  return $crl_db_id;
}

sub gen_listing{
  #my $insert_crl_text="insert ignore into "

  foreach my $crl_url (keys %crl) {
    if (not defined $crl{$crl_url}{'hosts'}) {
      print STDERR "skipping $crl_url\n";
      next
    }
    my @hosts=@{$crl{$crl_url}{'hosts'}};
    my $status=0;
    if(defined $crl{$crl_url}{'code'}) {
      $status=$crl{$crl_url}{'code'};
    }
    my $raw_size=0;
    if(defined $crl{$crl_url}{'raw_size'}){
      $raw_size=$crl{$crl_url}{'raw_size'};
    }
    my $issuer="";
    if(defined $crl{$crl_url}{'issuer'}){
      $issuer=$crl{$crl_url}{'issuer'};
    }
    print "\"$crl_url\", $status, $raw_size, ".scalar(@hosts).", \"$issuer\", \"".join(",",@hosts)."\"\n";
    #now the db

    insert_crl_into_db($crl_url,$status,$raw_size);



  };
  #print Dumper(%crl);
}

sub main{
  getopts("p:", \%options);
  my $password=$options{p};
  $dbh = DBI->connect('DBI:mysql:crl2', 'root', $password,
	            { RaiseError => 1, mysql_enable_utf8 => 1 }
	           ) or die;
  $dbh->begin_work   or die $dbh->errstr;

  get_crls();
  fetch_crls();
  gen_listing();


  $dbh->commit     or die $dbh->errstr;

}

main();
